CREATE TABLE  "AUTHORITY" 
   (	"AUTHORITYID" NUMBER(*,0) NOT NULL ENABLE, 
	"AUTHORITY" NVARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "PK_AUTHORITYID" PRIMARY KEY ("AUTHORITYID") ENABLE
   )
/
CREATE TABLE  "CLIENT" 
   (	"CLIENTID" NUMBER(*,0) NOT NULL ENABLE, 
	"FIRSTNAME" NVARCHAR2(30), 
	"LASTNAME" NVARCHAR2(30), 
	"PATRONYMIK" NVARCHAR2(30), 
	"PHONE" VARCHAR2(15), 
	"EMAIL" VARCHAR2(40), 
	 CONSTRAINT "PK_CLIENTID" PRIMARY KEY ("CLIENTID") ENABLE
   )
/
CREATE TABLE  "DISTRICT" 
   (	"DISTRICTID" NUMBER(*,0) NOT NULL ENABLE, 
	"NAME" NVARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "PK_DISTRICT" PRIMARY KEY ("DISTRICTID") ENABLE
   )
/
CREATE TABLE  "BUY_APPLICATION" 
   (	"APPLICATIONID" NUMBER(*,0) NOT NULL ENABLE, 
	"CREATE_DATE" DATE DEFAULT SYSDATE, 
	"CLIENTID" NUMBER(*,0) NOT NULL ENABLE, 
	"DISTRICTID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLOOR" NUMBER(3,0), 
	"PRICE_FROM" NUMBER(10,2), 
	"PRICE_TO" NUMBER(10,2) NOT NULL ENABLE, 
	"ROOMS" NUMBER(2,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_APPLICATION" PRIMARY KEY ("APPLICATIONID") ENABLE, 
	 CONSTRAINT "FK_DISTRICT" FOREIGN KEY ("DISTRICTID")
	  REFERENCES  "DISTRICT" ("DISTRICTID") ENABLE, 
	 CONSTRAINT "FK_CLIENT" FOREIGN KEY ("CLIENTID")
	  REFERENCES  "CLIENT" ("CLIENTID") ENABLE
   )
/
CREATE TABLE  "SALE_APPLICATION" 
   (	"APPLICATIONID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLATID" NUMBER(*,0) NOT NULL ENABLE, 
	"CLIENTID" NUMBER(*,0) NOT NULL ENABLE, 
	"CREATE_DATE" DATE DEFAULT sysdate, 
	 CONSTRAINT "PK_SALE_FLAT_ID" PRIMARY KEY ("APPLICATIONID") ENABLE, 
	 CONSTRAINT "FK_SALE_FLAT_FLAT" FOREIGN KEY ("FLATID")
	  REFERENCES  "FLAT" ("FLATID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "FK_CLIENT_SALE" FOREIGN KEY ("CLIENTID")
	  REFERENCES  "CLIENT" ("CLIENTID") ENABLE
   )
/
CREATE TABLE  "CONTRACT" 
   (	"CONTRACTID" NUMBER(*,0) NOT NULL ENABLE, 
	"CREATE_DATE" DATE DEFAULT sysdate, 
	"CONTRACT_NUMBER" NVARCHAR2(10) NOT NULL ENABLE, 
	"BUYID" NUMBER(*,0) NOT NULL ENABLE, 
	"SALEID" NUMBER(*,0) NOT NULL ENABLE, 
	"MONEY" NUMBER(10,2) NOT NULL ENABLE, 
	 CONSTRAINT "PK_CONTRACT" PRIMARY KEY ("CONTRACTID") ENABLE, 
	 CONSTRAINT "fk_buyid" FOREIGN KEY ("BUYID")
	  REFERENCES  "BUY_APPLICATION" ("APPLICATIONID") ENABLE, 
	 CONSTRAINT "fk_saleid" FOREIGN KEY ("SALEID")
	  REFERENCES  "SALE_APPLICATION" ("APPLICATIONID") ENABLE
   )
/
CREATE TABLE  "POST" 
   (	"POSTID" NUMBER(*,0) NOT NULL ENABLE, 
	"NAME" NVARCHAR2(30) NOT NULL ENABLE, 
	 CONSTRAINT "PK_POST" PRIMARY KEY ("POSTID") ENABLE
   )
/
CREATE TABLE  "EMPLOYEE" 
   (	"EMPLOYEEID" NUMBER(*,0) NOT NULL ENABLE, 
	"FIRSTNAME" NVARCHAR2(30), 
	"LASTNAME" NVARCHAR2(30), 
	"PATRONYMIK" NVARCHAR2(30), 
	"POSTID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY ("EMPLOYEEID") ENABLE, 
	 CONSTRAINT "FK_POST" FOREIGN KEY ("POSTID")
	  REFERENCES  "POST" ("POSTID") ENABLE
   )
/
CREATE TABLE  "FLAT" 
   (	"FLATID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLOOR" NUMBER(3,0) NOT NULL ENABLE, 
	"FLOORS_COUNT" NUMBER(3,0), 
	"SQUARE" NUMBER(5,2) NOT NULL ENABLE, 
	"ROOMS" NUMBER(1,0) NOT NULL ENABLE, 
	"LIVING_SQUARE" NUMBER(5,2), 
	"COOK_SQUARE" NUMBER(5,2), 
	"STREET" NVARCHAR2(30) NOT NULL ENABLE, 
	"HOUSE" NVARCHAR2(6) NOT NULL ENABLE, 
	"FLAT" NVARCHAR2(6), 
	"DISTRICTID" NUMBER(*,0) NOT NULL ENABLE, 
	"PRICE" NUMBER(10,2) NOT NULL ENABLE, 
	 CONSTRAINT "PK_FLAT" PRIMARY KEY ("FLATID") ENABLE, 
	 CONSTRAINT "FK_DISTRICTID_FLAT" FOREIGN KEY ("DISTRICTID")
	  REFERENCES  "DISTRICT" ("DISTRICTID") ENABLE
   )
/
CREATE TABLE  "SUITABLE_OPTIONS" 
   (	"OPTIONSID" NUMBER(*,0) NOT NULL ENABLE, 
	"APPLICATIONID" NUMBER(*,0) NOT NULL ENABLE, 
	"FLATID" NUMBER(*,0) NOT NULL ENABLE, 
	"STATUS" VARCHAR2(7), 
	 CONSTRAINT "CK_OPTIONS_STATUS" CHECK ((status = 'GOOD') or (status = 'BAD') or (status = 'NORMAL') or (status = 'NONE')) ENABLE, 
	 CONSTRAINT "PK_OPTIONSID" PRIMARY KEY ("OPTIONSID") ENABLE, 
	 CONSTRAINT "FK_APPLICATION_OPTIONS" FOREIGN KEY ("APPLICATIONID")
	  REFERENCES  "BUY_APPLICATION" ("APPLICATIONID") ON DELETE CASCADE ENABLE, 
	 CONSTRAINT "FK_FLAT_OPTIONS" FOREIGN KEY ("FLATID")
	  REFERENCES  "FLAT" ("FLATID") ON DELETE CASCADE ENABLE
   )
/
CREATE TABLE  "MEETING" 
   (	"MEETINGID" NUMBER(*,0) NOT NULL ENABLE, 
	"ONDATE" TIMESTAMP (6), 
	"OPTIONSID" NUMBER(*,0) NOT NULL ENABLE, 
	"STATUS" VARCHAR2(11), 
	 CONSTRAINT "PK_MEETING" PRIMARY KEY ("MEETINGID") ENABLE, 
	 CONSTRAINT "CH_MEETINGS_STATUS" CHECK ((status = 'FINISHED') or (status = 'PLANED')) ENABLE, 
	 CONSTRAINT "FK_OPTIONS_MEETING" FOREIGN KEY ("OPTIONSID")
	  REFERENCES  "SUITABLE_OPTIONS" ("OPTIONSID") ON DELETE CASCADE ENABLE
   )
/
CREATE TABLE  "USERS" 
   (	"USERSID" NUMBER(*,0) NOT NULL ENABLE, 
	"USERNAME" NVARCHAR2(30), 
	"PASSWORD" VARCHAR2(32) NOT NULL ENABLE, 
	"ENABLED" NUMBER(1,0) DEFAULT 0, 
	 CONSTRAINT "CH_ENABLED" CHECK ((enabled = 0) or (enabled = 1)) ENABLE, 
	 CONSTRAINT "PK_USERSID" PRIMARY KEY ("USERSID") ENABLE
   )
/
CREATE TABLE  "USER_AUTHORITY" 
   (	"USERSID" NUMBER(*,0) NOT NULL ENABLE, 
	"AUTHORITYID" NUMBER(*,0) NOT NULL ENABLE, 
	 CONSTRAINT "PK_USERAUTHORITY" PRIMARY KEY ("USERSID", "AUTHORITYID") ENABLE, 
	 CONSTRAINT "FK_USERS_AUTHORITY" FOREIGN KEY ("USERSID")
	  REFERENCES  "USERS" ("USERSID") ENABLE, 
	 CONSTRAINT "FK_AUTHORITY" FOREIGN KEY ("AUTHORITYID")
	  REFERENCES  "AUTHORITY" ("AUTHORITYID") ENABLE
   )
/

Rem No function found to generate DDL.

CREATE UNIQUE INDEX  "PK_APPLICATION" ON  "BUY_APPLICATION" ("APPLICATIONID")
/
CREATE UNIQUE INDEX  "PK_AUTHORITYID" ON  "AUTHORITY" ("AUTHORITYID")
/
CREATE UNIQUE INDEX  "PK_CLIENTID" ON  "CLIENT" ("CLIENTID")
/
CREATE UNIQUE INDEX  "PK_CONTRACT" ON  "CONTRACT" ("CONTRACTID")
/
CREATE UNIQUE INDEX  "PK_DISTRICT" ON  "DISTRICT" ("DISTRICTID")
/
CREATE UNIQUE INDEX  "PK_EMPLOYEE" ON  "EMPLOYEE" ("EMPLOYEEID")
/
CREATE UNIQUE INDEX  "PK_FLAT" ON  "FLAT" ("FLATID")
/
CREATE UNIQUE INDEX  "PK_MEETING" ON  "MEETING" ("MEETINGID")
/
CREATE UNIQUE INDEX  "PK_OPTIONSID" ON  "SUITABLE_OPTIONS" ("OPTIONSID")
/
CREATE UNIQUE INDEX  "PK_POST" ON  "POST" ("POSTID")
/
CREATE UNIQUE INDEX  "PK_SALE_FLAT_ID" ON  "SALE_APPLICATION" ("APPLICATIONID")
/
CREATE UNIQUE INDEX  "PK_USERAUTHORITY" ON  "USER_AUTHORITY" ("USERSID", "AUTHORITYID")
/
CREATE UNIQUE INDEX  "PK_USERSID" ON  "USERS" ("USERSID")
/

Rem No package found to generate DDL.

CREATE OR REPLACE PROCEDURE  "FIND_SUITABLE_OPTIONS" (applicationid in number) as district number; floor number; price_from number; price_to number; rooms number; begin select fb.districtid,fb.rooms,fb.price_to,fb.price_from,fb.floor into district,rooms,price_to,price_from,floor from buy_application fb where fb.applicationid = applicationid; if floor > 0 then begin find_suitable_options_f(district,price_from,price_to,rooms,floor,applicationid);end; else begin find_suitable_options_nf(district,price_from,price_to,rooms,applicationid);end; end if; end find_suitable_options;
/
 CREATE OR REPLACE PROCEDURE  "FIND_SUITABLE_OPTIONS_F" (district in number,price_f in number,price_t in number,rooms in number,floor in number,appid in number) as cursor flats is select f.flatid from flat f where f.rooms = rooms and f.districtid = district and f.price >= price_f and f.price <= price_t and f.floor = floor and f.flatid not in (select flatid from suitable_options where applicationid = appid); flatid number; begin open flats; loop fetch flats into flatid; exit when flats%notfound; insert into suitable_options (applicationid,flatid) values(appid,flatid); end loop; close flats; end find_suitable_options_f;
/
 CREATE OR REPLACE PROCEDURE  "FIND_SUITABLE_OPTIONS_NF" (district in number,price_f in number,price_t in number,rooms in number,appid in number) as cursor flats is select f.flatid from flat f where f.rooms = rooms and f.districtid = district and f.price >= price_f and f.price <= price_t and flatid not in (select flatid from suitable_options where applicationid = appid); flatid number; begin open flats; loop fetch flats into flatid; exit when flats%notfound; insert into suitable_options (applicationid,flatid) values(appid,flatid); end loop; close flats; end find_suitable_options_nf;
/
 

 CREATE SEQUENCE   "HIBERNATE_SEQUENCE"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 57 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "MEETING_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 8 NOCACHE  NOORDER  NOCYCLE
/
 CREATE SEQUENCE   "SUITABLE_OPTOINS_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  NOORDER  NOCYCLE
/

Rem No synonym found to generate DDL.

CREATE OR REPLACE TRIGGER  "BUYAPP_INSERT_TRIGGER" 
before insert on buy_application for each row
begin
if :NEW.create_date is null then
select sysdate into :NEW.create_date from dual;
end if;
end;
/
ALTER TRIGGER  "BUYAPP_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "MEETING_INSERT_TRIGGER" 

before insert on meeting for each row

declare
already_exists exception;
plus_hour timestamp;
minus_hour timestamp;
count_meets number;
begin
select :New.ondate + INTERVAL '1' HOUR into plus_hour from dual;
select :New.ondate - INTERVAL '1' HOUR into minus_hour from dual;
select count(m.meetingid) into count_meets from meeting m join suitable_options so on so.optionsid = m.optionsid join flat f on f.flatid = so.flatid where m.ondate > minus_hour and m.ondate < plus_hour; 
if count_meets > 0 then
raise already_exists;
end if;
select meeting_seq.nextval into :new.meetingid from dual;
if :NEW.status is null then
  select 'PLANED' into :new.status from dual;
end if;
end;
/
ALTER TRIGGER  "MEETING_INSERT_TRIGGER" ENABLE
/
CREATE OR REPLACE TRIGGER  "SALEAPP_INSERT_TRIGGER" 
before insert on sale_application for each row
begin
if :NEW.create_date is null then
select sysdate into :NEW.create_date from dual;
end if;
end;
/
ALTER TRIGGER  "SALEAPP_INSERT_TRIGGER" ENABLE
/
